# -*- coding: utf-8 -*-
# @Time    : 2024/7/1 17:05
# @Author  : yujiahao
# @File    : 00_pymysql_curd.py
# @description:pymysql的增删改查


"""
Python 接入 MySQL 数据库

在 Python3 中，我们可以使用 mysqlclient 或者 pymysql 三方库来接入 MySQL 数据库并实现数据持久化操作。二者的用法完全相同，只是导入的模块名不一样。
我们推荐大家使用纯 Python 的三方库 pymysql，因为它更容易安装成功。


【接入 MySQL】
首先，我们可以在命令行或者 PyCharm 的终端中通过下面的命令安装 pymysql，如果需要接入 MySQL 8，还需要安装一个名为 cryptography 的三方库来支持 MySQL 8 的密码认证方式。

    pip install pymysql cryptography

使用 pymysql 操作 MySQL 的步骤如下所示：基本的操作流程和JDBC差不多

1. 创建连接。
    MySQL 服务器启动后，提供了基于 TCP （传输控制协议）的网络服务。我们可以通过 pymysql 模块的 connect 函数连接 MySQL 服务器。在调用 connect 函数时，需要指定主机（host）、端口（port）、用户名（user）、口令（password）、数据库（database）、字符集（charset）等参数，该函数会返回一个 Connection 对象。

2. 获取游标。
    连接 MySQL 服务器成功后，接下来要做的就是向数据库服务器发送 SQL 语句，MySQL 会执行接收到的 SQL 并将执行结果通过网络返回。要实现这项操作，需要先通过连接对象的 cursor 方法获取游标（Cursor）对象。

3. 发出 SQL。
    通过游标对象的 execute 方法，我们可以向数据库发出 SQL 语句。

4. 如果执行 insert、delete 或 update 操作，需要根据实际情况提交或回滚事务。
    因为创建连接时，默认开启了事务环境，在操作完成后，需要使用连接对象的 commit 或 rollback 方法，实现事务的提交或回滚，rollback 方法通常会放在异常捕获代码块 except 中。如果执行 select 操作，需要通过游标对象抓取查询的结果，对应的方法有三个，分别是：fetchone、fetchmany 和 fetchall。其中 fetchone 方法会抓取到一条记录，并以元组或字典的方式返回；fetchmany 和 fetchall 方法会抓取到多条记录，以嵌套元组或列表装字典的方式返回。

5. 关闭连接。
    在完成持久化操作后，请不要忘记关闭连接，释放外部资源。我们通常会在 finally 代码块中使用连接对象的 close 方法来关闭连接。
"""

import pymysql


# 简单看一个增删改查，这里不说优化

def pymysql_conn():
    # todo 1.1、创建连接
    get_conn = pymysql.connect(
        host='127.0.0.1',
        port=33061,
        user='***',
        passwd='***',
        db='bigdata_test',
        charset='utf8',
        autocommit=True  # 自动提交事物,建议大家手动提交或回滚
    )
    return get_conn


# todo 1、增
def pymysql_insert(sql, params=None):
    # todo 1.1、创建连接
    conn = pymysql_conn()
    try:
        # todo 1.3、获取游标
        with conn.cursor() as cursor:
            # todo 1.4、执行SQL
            if params:
                # 如果要批量插入可以用这个游标对象的executemany方法，第一个参数仍然是 SQL 语句，第二个参数可以是包含多组数据的列表或元组
                cursor.executemany(sql, params)
            else:
                cursor.execute(sql)
            # todo 1.5、手动事务提交
            conn.commit()
    except pymysql.MySQLError as e:
        print(f"错误：{e}")
        conn.rollback()
    finally:
        # todo 1.2、关闭连接
        conn.close()


# todo 2、查
def pymysql_query(sql, params=None):
    # todo 2.1、创建连接
    conn = pymysql_conn()
    try:
        # todo 2.3、获取游标
        with conn.cursor() as cursor:
            # todo 2.4、执行SQL
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            # todo 2.5、获取查询结果
            '''
            我们通过构造一个while循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。
            因为如果使用fetchall一次性将所有记录抓取到一个嵌套元组中，会造成非常大的内存开销，这在很多场景下并不是一个好主意。
            如果不愿意使用while循环，还可以考虑使用iter函数构造一个迭代器来逐行抓取数据。
            '''

            # ①逐行抓取
            row = cursor.fetchone()
            while row:
                print(row)
                row = cursor.fetchone()
            # ② 可以抓全部
            # print(cursor.fetchall())
            # ③ 可以抓几条
            # print(cursor.fetchmany(2))

    except pymysql.MySQLError as e:
        print(f"错误：{e}")
    finally:
        # todo 2.2、关闭连接
        conn.close()


# todo 3、删
def pymysql_delete(sql, params=None):
    # todo 3.1、创建连接
    conn = pymysql_conn()
    try:
        # todo 3.3、获取游标
        with conn.cursor() as cursor:
            # todo 3.4、执行SQL
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            # todo 3.5、手动事务提交
            '''
            说明：如果不希望每次 SQL 操作之后手动提交或回滚事务，可以connect函数中加一个名为autocommit的参数并将它的值设置为True，
            表示每次执行 SQL 成功后自动提交。但是我们建议大家手动提交或回滚，这样可以根据实际业务需要来构造事务环境。如果不愿意捕获异常并进行处理，
            可以在try代码块后直接跟finally块，省略except意味着发生异常时，代码会直接崩溃并将异常栈显示在终端中
            '''
            conn.commit()
    except pymysql.MySQLError as e:
        print(f"错误：{e}")
        conn.rollback()
    finally:
        # todo 3.2、关闭连接
        conn.close()


# todo 4、改
def pymysql_update(sql, params=None):
    # todo 4.1、创建连接
    conn = pymysql_conn()
    try:
        # todo 4.3、获取游标
        with conn.cursor() as cursor:
            # todo 4.4、执行SQL
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            # todo 4.5、手动事务提交
            conn.commit()
    except pymysql.MySQLError as e:
        print(f"错误：{e}")
        conn.rollback()
    finally:
        # todo 4.2、关闭连接
        conn.close()


def main():
    # 测试数据
    create_table_sql = """
    create table if not exists bigdata_test.yujiahao (
        id int auto_increment primary key,
        name varchar(255) not null,
        age int not null,
        department varchar(255) not null
    )
    """

    single_insert_sql = """
    insert into bigdata_test.yujiahao (name, age, department) values 
    ('bob', 25, 'engineering'),
    ('charlie', 35, 'sales'),
    ('david', 28, 'marketing');
    """

    select_sql = """
    select * from bigdata_test.yujiahao;
    """

    update_sql = """
    update bigdata_test.yujiahao set age = 26, department = 'product' where name = 'bob';
    """

    delete_sql = """
    delete from bigdata_test.yujiahao where age < 30;
    """
    batch_insert_sql = "INSERT INTO yujiahao (name, age, department) VALUES (%s, %s, %s)"
    batch_data = [
        ('张学友', 30, '歌手'),
        ('张国荣', 25, '演员'),
        ('林青霞', 35, '演员')
    ]

    # 调用方法

    # pymysql_insert(create_table_sql)
    # 单条插入
    # pymysql_insert(single_insert_sql)
    # 批量插入
    # pymysql_insert(batch_insert_sql, batch_data)
    # pymysql_query(select_sql)
    # pymysql_delete(delete_sql)
    pymysql_update(update_sql)


if __name__ == '__main__':
    main()
